import pandas as pd
import numpy as np
from sklearn.linear_model import LinearRegression
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
import os

from SKO.AbstractDPJob import AbstractDPJob
class Predict_6628_2Job(AbstractDPJob):
    def __init__(self,
                 p_pret_s_aim=None,p_recv_s=None):
        super(Predict_6628_2Job, self).__init__()
        self.pret_s_aim = p_pret_s_aim
        self.recv_s = p_recv_s

        pass
    def execute(self):
        return self.do_execute()
    def do_execute(self):
        super(Predict_6628_2Job, self).do_execute()
        pret_s_aim = self.pret_s_aim
        recv_s = self.recv_s
        # 预测一炼钢硫平衡接口传入参数
        # KR目标硫
        # pret_s_aim = 14
        msg = ''
        # 读数据，本地文件
        # if os.getenv('AG_ENV_PYTHON_PATH') is None:
        #     ROOT_DIR = os.path.dirname(os.path.abspath(__file__)) + os.sep
        # else:
        #     ROOT_DIR = os.getcwd() + os.sep
        #     # ROOT_DIR = WORK_DIR + "ag_art" + os.sep
        # parent_dir = os.path.dirname(ROOT_DIR)
        # import importlib.util
        # xlsx_name = parent_dir + '/KRdata1.xlsx'
        xlsx_name = 'D:/repos/sicost/KRdata1.xlsx'
        df = pd.read_excel(xlsx_name)
        col_list = []
        # col_list.append('实绩石灰量')
        col_list.append('受铁坑温度')
        col_list.append('KR后温度1')
        col_list.append('KR目标硫')
        col_list.append('KR处理前硫,ppm')
        col_list.append('KR搅拌时间,s')
        print(col_list)
        df0 = df[col_list]
        # df0.rename(columns={'实绩石灰量': 'CAO_WT'}, inplace=True)
        df0.rename(columns={'受铁坑温度': 'TEMP_1'}, inplace=True)
        df0.rename(columns={'KR后温度1': 'TEMP_2'}, inplace=True)
        df0.rename(columns={'KR目标硫': 'KR_S_AIM'}, inplace=True)
        df0.rename(columns={'KR处理前硫,ppm': 'FE_S'}, inplace=True)
        df0.rename(columns={'KR搅拌时间,s': 'WHISK_TIME'}, inplace=True)

        # 根据KR目标S，对数据进行分组
        if pret_s_aim == 14:
            df1 = df0[df0['KR_S_AIM'] == 14]
        elif pret_s_aim == 24:
            df1 = df0[df0['KR_S_AIM'] == 24]
        elif pret_s_aim == 34:
            df1 = df0[df0['KR_S_AIM'] == 34]
        elif pret_s_aim == 54:
            df1 = df0[df0['KR_S_AIM'] == 54]
        else:
            df1 = df0[(df0['KR_S_AIM'] != 14) & (df0['KR_S_AIM'] != 24)
                      & (df0['KR_S_AIM'] != 34) & (df0['KR_S_AIM'] != 54)
                      ]

        def clean_data(df, gamma):
            column_name_list = df.columns.tolist()
            column_name_num = len(column_name_list)
            clean_str_start = 'df_new = df['
            clean_str_end = ']'
            ldict1 = {}
            for i in range(0, column_name_num):
                # print(i)
                # print(column_name_list[i])
                column_name_tmp = column_name_list[i]
                exec('''clean_str3 = "(df['{}'] > 0)"'''.format(column_name_tmp), locals(), ldict1)
                clean_str3 = ldict1["clean_str3"]
                if i == 0:
                    clean_str_start = clean_str_start + clean_str3
                else:
                    clean_str_start = clean_str_start + ' & ' + clean_str3
            clean_str = clean_str_start + clean_str_end
            # print(clean_str)
            exec(clean_str, locals(), ldict1)
            df_new = ldict1["df_new"]
            df_new = df_new.reset_index(drop=True)
            return df_new

        gamma = 1.5
        df1_clean1 = clean_data(df1, gamma)

        df1_clean1['TEMP'] = df1_clean1['TEMP_1'] - df1_clean1['TEMP_2']
        df1_clean2 = df1_clean1[(df1_clean1['TEMP'] > 0)]
        df1_clean2 = df1_clean2.reset_index(drop=True)
        df1_clean2.drop(['TEMP_1'], axis=1, inplace=True)
        df1_clean2.drop(['TEMP_2'], axis=1, inplace=True)
        df1_clean2.rename(columns={'FE_S': 'RECV_S'}, inplace=True)

        def average(num1, num2):
            return (num1 + num2) / 2

        recv_s_max_list2 = [0, 200, 300, 400, 500]

        df_out2 = pd.DataFrame(
            columns=['RECV_S_MAX', 't1', 't2'])
        dict = {}
        for i in range(0, len(recv_s_max_list2)):
            print(recv_s_max_list2[i])
            if i == 0:
                interval = recv_s_max_list2[i + 1] - recv_s_max_list2[i]
            else:
                interval = recv_s_max_list2[i] - recv_s_max_list2[i - 1]
            min_tmp = recv_s_max_list2[i] - interval / 10
            max_tmp = recv_s_max_list2[i] + interval / 10
            df7_copy = df1_clean2.copy()
            df7_copy['DELTA_RECV_S'] = abs(df7_copy['RECV_S'] - recv_s_max_list2[i])
            df7_copy_sorted = df7_copy.sort_values(by='DELTA_RECV_S')
            # , ascending = False
            df7_copy_sorted = df7_copy_sorted.reset_index(drop=True)
            df7_tmp2 = df7_copy_sorted.head(10)
            df7_tmp1 = df7_copy[(df7_copy['RECV_S'] <= max_tmp) & (df7_copy['RECV_S'] >= min_tmp)]
            df7_tmp3 = df7_copy[(df7_copy['RECV_S'] == recv_s_max_list2[i])]
            row_count1 = len(df7_tmp1)
            row_count3 = len(df7_tmp3)
            if row_count3 > 0:
                y1_pred = df7_tmp3['TEMP'].mean()
                print(y1_pred)
            else:
                y1_pred = 0
            dict['RECV_S_MAX'] = recv_s_max_list2[i]
            dict['t1'] = y1_pred
            if row_count1 <= 5:
                df7_tmp = df7_tmp2
            else:
                df7_tmp = df7_tmp1
            model = LinearRegression()
            X = df7_tmp['RECV_S'].values.reshape(-1, 1)
            y = df7_tmp['TEMP'].values
            model.fit(X, y)
            list_tmp = []
            list_tmp.append(recv_s_max_list2[i])
            array_tmp = np.array(list_tmp).reshape(-1, 1)
            y_pred = model.predict(array_tmp)
            # print(y_pred)
            y1_pred = y_pred[0]
            print(y1_pred)
            dict['t2'] = y1_pred
            new_row = pd.Series(dict)
            df_out2 = df_out2.append(new_row, ignore_index=True)
        df_out2_copy = df_out2.copy()
        df_out2_copy = df_out2_copy[df_out2_copy['t1'] != 0]
        df_out2_copy = df_out2_copy.reset_index(drop=True)
        model = LinearRegression()
        X = df_out2_copy['RECV_S_MAX'].values.reshape(-1, 1)
        y = df_out2_copy['t1'].values
        model.fit(X, y)
        for index, row in df_out2.iterrows():
            if row['t1'] == 0:
                x_pred = row['RECV_S_MAX']
                list_tmp = []
                list_tmp.append(x_pred)
                array_tmp = np.array(list_tmp).reshape(-1, 1)
                y_pred = model.predict(array_tmp)
                # print(y_pred)
                y1_pred = y_pred[0]
                row['t1'] = y1_pred
        print(df_out2)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[0], 't1']
        t1_min = float(values)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[0], 't2']
        t2_min = float(values)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[-1], 't1']
        t1_max = float(values)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[-1], 't2']
        t2_max = float(values)
        t_min = min(t1_min, t2_min)
        t_max = max(t1_max, t2_max)
        df_out2['t3'] = df_out2['t2']
        df_out2['t4'] = df_out2['t2']
        for index, row in df_out2.iterrows():
            if index == 0:
                row['t3'] = t_min
                row['t4'] = t_min
            elif index == len(recv_s_max_list2) - 1:
                row['t3'] = t_max
                row['t4'] = t_max
            else:
                t3_tmp = t_min + (t_max - t_min) / (recv_s_max_list2[-1] - recv_s_max_list2[0]) * (
                        row['RECV_S_MAX'] - recv_s_max_list2[0])
                row['t3'] = t3_tmp
        df_out2['t4_next'] = df_out2['t4'].shift(-1)
        df_out2.t4_next.fillna(99999, inplace=True)
        df_out2['t4_pred'] = df_out2['t4'].shift(1)
        df_out2.t4_pred.fillna(0, inplace=True)
        change_value = 0
        df_out2['t_tmp'] = df_out2['t4']
        for index, row in df_out2.iterrows():
            if index != 0 and index != len(recv_s_max_list2) - 1:
                # 检验
                print(row['t4_pred'])
                print(change_value)
                if row['t4'] > change_value and row['t4'] < row['t4_next']:
                    change_value = row['t4']
                else:
                    print('需要改变')
                    new_value1 = average(row['t3'], row['t4'])
                    if new_value1 > change_value and new_value1 < row['t4_next']:
                        change_value = new_value1
                    else:
                        new_value2 = average(row['t3'], new_value1)
                        if new_value2 > change_value and new_value2 < row['t4_next']:
                            change_value = new_value2
                        else:
                            change_value = row['t3']
            else:
                change_value = row['t4']
            row['t_tmp'] = change_value
        df_out2 = df_out2[['RECV_S_MAX', 't_tmp']]
        df_out2.rename(columns={'t_tmp': 'TEMP'}, inplace=True)
        print(df_out2)
        row3 = df_out2[df_out2['RECV_S_MAX'] == 300]
        value3 = row3['TEMP'].values[0] if not row3.empty else 0
        row4 = df_out2[df_out2['RECV_S_MAX'] == 500]
        value4 = row4['TEMP'].values[0] if not row4.empty else 0
        temp_unit_1 = (value4 - value3) / 20
        df_out2['RECV_S_MAX_NEXT'] = df_out2['RECV_S_MAX'].shift(-1)
        df_out2['TEMP_NEXT'] = df_out2['TEMP'].shift(-1)
        df_out2.RECV_S_MAX_NEXT.fillna(99999, inplace=True)
        df_out2.TEMP_NEXT.fillna(99999, inplace=True)

        def cal_temp(p_recv_s):
            recv_s = p_recv_s

            for index, row in df_out2.iterrows():
                recv_s_max_tmp = row['RECV_S_MAX']
                temp_max_tmp = row['TEMP']
                recv_s_max_next_tmp = row['RECV_S_MAX_NEXT']
                temp_max_next_tmp = row['TEMP_NEXT']
                print(index)
                if recv_s <= 500:
                    temp_unit = (temp_max_next_tmp - temp_max_tmp) / (
                            recv_s_max_next_tmp - recv_s_max_tmp) * 10
                else:
                    temp_unit = temp_unit_1
                if index == 0:
                    if recv_s <= recv_s_max_tmp:
                        temp_tmp = temp_max_tmp - (recv_s_max_tmp - recv_s) * temp_unit / 10
                        break
                    elif recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                        temp_tmp = temp_max_tmp + (recv_s - recv_s_max_tmp) * temp_unit
                        break
                    else:
                        continue
                else:
                    if recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                        temp_tmp = temp_max_tmp + (recv_s - recv_s_max_tmp) * temp_unit / 10
                        break
                    else:
                        continue
            return temp_tmp

        wenjiang = cal_temp(recv_s * 10)
        print(wenjiang)
        return wenjiang











